﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace Persistence
{
    public class DBConnection
    {
        #region SINGLETON
        private static DBConnection _dbConn;

        public static DBConnection getInstance() {
            if (_dbConn == null) {
                _dbConn = new DBConnection();
            }
            return _dbConn;
        }
        
        #endregion

        #region CONSTRUCTOR

        private DBConnection() { }

        #endregion

        public MySqlCommand createCommand()
        {
            string _connString = " server = localhost; user = vsuser; password = admin; database = academymanagerdb";
            MySqlConnection _conn = new MySqlConnection();
            _conn.ConnectionString = _connString;
            MySqlCommand _command = _conn.CreateCommand();
            return _command;
        }

        public DataTable executeSelectCommand(MySqlCommand comm)
        {
            DataTable _table;
            try
            {
                comm.Connection.Open();
                MySqlDataReader _reader = comm.ExecuteReader();
                _table = new DataTable();
                _table.Load(_reader);
                _reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                comm.Connection.Close();
            }
            return _table;
        }

        public void executeNonQueryCommand(MySqlCommand comm)
        {
            try
            {
                comm.Connection.Open();
                int x = comm.ExecuteNonQuery();
                //if (x == 0) {
                //    throw new Exception("Error al ejecutar el comando");
                //}
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                comm.Connection.Close();
            }
        }

        public string executeScalarCommand(MySqlCommand comm)
        {
            string resp = "";
            try
            {
                comm.Connection.Open();
                resp = comm.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                comm.Connection.Close();
            }
            return resp;
        }
    }
}
